﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MixScript.BLL.FI
{
    public class cIncomeStatetment : cBaseBLL
    {
        public cIncomeStatetment() { }


        public DataTable ListOfData()
        {
            String sSQL = @"SELECT g.groupnum,g.groupname
                            FROM accgroup g WHERE g.aplication > 3 ";
            // Seharusnya : FROM accgroup g WHERE g.aplication > 3 ";
            return Dbserver.ExecuteQuery(sSQL, "IS");
        }
        public DataTable SubListOfData(String Groupnum)
        {
            String sSQL = @"Select * from accsubgroup s where  s.groupnum = '{0}' ";
            return Dbserver.ExecuteQuery(String.Format(sSQL, Groupnum), "IS");
        }
        public Decimal getCurrent(String acctnum)
        {
            DataTable dt = new DataTable();
            String sSQL = @"SELECT d.acctnum AS acctnum,
                            SUM(CASE d.dbcd WHEN 'C' THEN 
	                                    CASE a.aplication WHEN  4 THEN d.valconvert 
	                                    WHEN 6 THEN  d.valconvert  ELSE 0 END
                                    WhEN 'D' THEN 
	                                    CASE a.aplication WHEN  5 THEN d.valconvert 
	                                    WHEN 7 THEN  d.valconvert  ELSE 0 END
                                    END)
                                    AS [current] 
                                    FROM accslipdet d
                                    LEFT JOIN accsliphdr h ON d.header_id=h.slipid
                                    LEFT JOIN accaccounts a ON d.acctnum=a.acctnum
                                    LEFT JOIN accdailykurs k ON h.slipdate=k.date AND k.currency='USD'
                                    WHERE a.aplication>3 AND h.posting LIKE 1 AND d.acctnum= '{0}'
                                    --AND slipdate BETWEEN FROM_UNIXTIME($periode_unix) AND FROM_UNIXTIME($lastdate_unixm)
                                    GROUP BY d.acctnum ";
            dt = Dbserver.ExecuteQuery(String.Format(sSQL, acctnum), "Current");

            if (dt.Rows.Count > 0)
                return (Decimal)dt.Rows[0].ItemArray[1];
            else
                return 0;
        }
        public Decimal getAccumulated(String acctnum)
        {
            DataTable dt = new DataTable();
            String sSQL = @"SELECT d.acctnum AS acctnum,
                            SUM(CASE d.dbcd WHEN 'C' THEN 
	                                    CASE a.aplication WHEN  4 THEN d.valconvert 
	                                    WHEN 6 THEN  d.valconvert  ELSE 0 END
                                    WhEN 'D' THEN 
	                                    CASE a.aplication WHEN  5 THEN d.valconvert 
	                                    WHEN 7 THEN  d.valconvert  ELSE 0 END
                                    END)
                                    AS [current] 
                                    FROM accslipdet d
                                    LEFT JOIN accsliphdr h ON d.header_id=h.slipid
                                    LEFT JOIN accaccounts a ON d.acctnum=a.acctnum
                                    LEFT JOIN accdailykurs k ON h.slipdate=k.date AND k.currency='USD'
                                    WHERE a.aplication>3 AND h.posting LIKE 1 AND d.acctnum= '{0}'
                                    --AND slipdate BETWEEN FROM_UNIXTIME($periode_unix) AND FROM_UNIXTIME($lastdate_unixm)
                                    GROUP BY d.acctnum ";
            dt = Dbserver.ExecuteQuery(String.Format(sSQL, acctnum), "Current");

            if (dt.Rows.Count > 0)
                return (Decimal)dt.Rows[0].ItemArray[1];
            else
                return 0;
        }

    }
}
